select @documentFolderType:=FolderTypeId from FolderType where Name='Document';
select @docTypeFolderType:=FolderTypeId from FolderType where Name='DocType';

select @rootDocumentFolder:=FolderId from Folder where FolderType=@documentFolderType and IsNull(ParentFolder);
select @rootDocTypeFolder:=FolderId from Folder where FolderType=@docTypeFolderType AND IsNull(ParentFolder);

select @userRole:=DcRoleId from DcRole where RoleName='Users';

select @docCircAdmin:=DcUserId from DcUser where Login='DocCircAdmin';
select @docCircUser1:=DcUserId from DcUser where Login='DocCircUser1';
select @docCircUser2:=DcUserId from DcUser where Login='DocCircUser2';
select @docCircUser3:=DcUserId from DcUser where Login='DocCircUser3';

select @notificationRIT:=RouteItemTypeId from RouteItemType where Name='Notification';
select @decisionRIT:=RouteItemTypeId from RouteItemType where Name='Decision';

select @notSavedDS:=DocStatusId from DocStatus where Name='NotSavedDocStatus';
select @draftDS:=DocStatusId from DocStatus where Name='DraftDocStatus';
select @underConsDS:=DocStatusId from DocStatus where Name='UnderConsDocStatus';
select @finalizedDS:=DocStatusId from DocStatus where Name='FinalizedDocStatus';

create temporary table if not exists tmpDocCard1RouteItem (DocCard bigint(20), RouteItem int(11) not null, 
	Passed tinyint(1) not null);
create temporary table if not exists tmpDocCard3RouteItem (DocCard bigint(20), RouteItem int(11) not null, 
	Passed tinyint(1) not null);
create temporary table if not exists tmpDocCard5RouteItem (DocCard bigint(20), RouteItem int(11) not null, 
	Passed tinyint(1) not null);

-- Add DocType: DocType1 "Test type first" into rootDocTypeFolder by DocCircAdmin
insert into DocType (DocTypeId, Folder, Name, Description, Prefix, NextNumber, IsNew, DcUser) values 
(default, @rootDocTypeFolder, 'Test type first', 'first type', 'TTF', 1, false, @docCircAdmin); 
set @docType1:=last_insert_id(); 
--
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType1, 1, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType1, @docCircUser1, @decisionRIT, 0);
	insert into tmpDocCard1RouteItem ( RouteItem, Passed) Values (last_insert_id(), true);
	insert into tmpDocCard3RouteItem ( RouteItem, Passed) Values (last_insert_id(), false);
	insert into tmpDocCard5RouteItem ( RouteItem, Passed) Values (last_insert_id(), true);
	
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType1, 2, '[0]&&[1]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType1, @DocCircUser2, @decisionRIT, 0);
	set @lastRouteItem:=last_insert_id();
	insert into tmpDocCard1RouteItem ( RouteItem, Passed) Values (@lastRouteItem, true);
	insert into tmpDocCard5RouteItem ( RouteItem, Passed) Values (@lastRouteItem, false);
	
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType1, @docCircUser3, @decisionRIT, 1);
	set @lastRouteItem:=last_insert_id();
	insert into tmpDocCard1RouteItem ( RouteItem, Passed) Values (@lastRouteItem, false);
	insert into tmpDocCard5RouteItem ( RouteItem, Passed) Values (@lastRouteItem, false);
	
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType1, 3, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType1, @docCircAdmin, @decisionRIT, 0);
--
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO)
	select @docType1, DcRole, C, R, U, D, RO, UO, DO from NewDocTypePermission;
	
	
create temporary table if not exists tmpDocCard2RouteItem (DocCard bigint(20), RouteItem int(11) not null, 
	Passed tinyint(1) not null);
create temporary table if not exists tmpDocCard4RouteItem (DocCard bigint(20), RouteItem int(11) not null, 
	Passed tinyint(1) not null);
		
-- Add DocType: DocType2 "Test type second" into rootDocTypeFolder by DocCircAdmin
insert into DocType (DocTypeId, Folder, Name, Description, Prefix, NextNumber, IsNew, DcUser) values 
(default, @rootDocTypeFolder, 'Test type second', 'second type', 'TTS', 1, false, @docCircAdmin); 
set @docType2:=last_insert_id(); 
--
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType2, 1, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType2, @docCircUser1, @decisionRIT, 0);
	set @lastRouteItem:=last_insert_id();
	insert into tmpDocCard2RouteItem ( RouteItem, Passed) Values (@lastRouteItem, true);
	insert into tmpDocCard4RouteItem ( RouteItem, Passed) Values (@lastRouteItem, true);
	
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType2, 2, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType2, @DocCircUser2, @decisionRIT, 0);
	set @lastRouteItem:=last_insert_id();
	insert into tmpDocCard2RouteItem ( RouteItem, Passed) Values (@lastRouteItem, false);
	insert into tmpDocCard4RouteItem ( RouteItem, Passed) Values (@lastRouteItem, true);
	
	insert into RoutePoint (DocType, OrderNum, Expression) Values
		(@docType2, 3, '[0]');
	set @lastRoutePoint:=last_insert_id();
	insert into RouteItem (RoutePoint, DocType, DcUser, RouteItemType, ItemIndex) Values 
		(@lastRoutePoint, @docType2, @docCircAdmin, @decisionRIT, 0);
	set @lastRouteItem:=last_insert_id();
	insert into tmpDocCard4RouteItem ( RouteItem, Passed) Values (@lastRouteItem, false);
--
insert into DocTypePermission (DocType, DcRole, C, R, U, D, RO, UO, DO)
	select @docType2, DcRole, C, R, U, D, RO, UO, DO from NewDocTypePermission;	
	
	

-- Add new document folder into the root doc folder: Test document folder1
insert into Folder (ParentFolder, Name, FolderType) 
	Values (@rootDocumentFolder, 'Test document folder1', @documentFolderType);
set @testDocumentFolder1:=last_insert_id();
insert into FolderPermission (DcRole, Folder, C,R,U,D,B,RO,UO,DO)
	select DcRole, @testDocumentFolder1, C,R,U,D,B,RO,UO,DO from NewFolderPermission 
	where FolderType=@documentFolderType;


-- Add new Institutions: Institution1, Institution2
insert into institution (DcUser, Name, Phone1, Phone2, Address1, Address2, Email1, Email2, WebSite, Note)
	Values (@docCircUser1, 'Institution1', '1111-1', '1111-2', 'FirstAddressOfInst1', 'SecondAddressOfInst1', 
	'inst11@test.org', 'inst12@test.org', 'inst1.test.org', 'the best institution');
select @institution1:=last_insert_id();

insert into institution (DcUser, Name, Phone1, Phone2, Address1, Address2, Email1, Email2, WebSite, Note)
	Values (@docCircUser1, 'Institution2', '2222-1', '2222-2', 'FirstAddressOfInst2', 'SecondAddressOfInst2', 
	'inst21@test.org', 'inst22@test.org', 'inst2.test.org', 'a better institution');
select @institution2:=last_insert_id();


-- Add DocCard: DocCard1 "Test DocCard1" into rootDocumentFolder by DocCircAdmin
-- under consideration by User3, passed User1, User2 RoutePointNum=2
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@rootDocumentFolder, @underConsDS, @docCircAdmin, @docCircAdmin, 'Test DocCard1', 'TTF000001', 
    '2007-05-24 11:35:00', false, 'T21', 'MS', 'Evpo', 
    false, 2,
    @docType1, '2007-05-23 12:00:00', '2007-05-01', 'this is the note of DocCard1',
    @institution1, null
    );

set @docCard1:=last_insert_id();
insert into DocCardRouteItem (DocCard, RouteItem, Passed) 
	select @docCard1, RouteItem, Passed from tmpDocCard1RouteItem;


-- Add DocCard: DocCard2 "Test DocCard2" into testDocumentFolder1 by DocCircUser2
-- under consideration by User2, passed User1 RoutePointNum=2
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@testDocumentFolder1, @underConsDS, @docCircAdmin, @docCircUser2, 'Test DocCard2', 'TTS000002', 
    '2007-02-24 11:35:00', false, 'T232', 'MS2', 'Evpo2', 
    false, 2,
    @docType2, '2007-02-23 12:00:00', '2007-02-01', 'this is the note of DocCard2',
    @institution1, @institution2
    );

set @docCard2:=last_insert_id();
insert into DocCardRouteItem (DocCard, RouteItem, Passed) 
	select @docCard2, RouteItem, Passed from tmpDocCard2RouteItem;

-- Add DocCard: DocCard3 "Test document card" into rootDocumentFolder by DocCircAdmin
-- under consideration by User1 RoutePointNum=1
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@rootDocumentFolder, @underConsDS, @docCircUser2, @docCircAdmin, 'Test DocCard3', 'TTF000003', 
    '2007-03-24 11:35:00', false, 'T213', 'MS3', 'Evpo3', 
    false, 1,
    @docType1, '2007-03-23 12:00:00', '2007-03-01', 'this is the note of DocCard3',
    @institution2, @institution1
    );

set @docCard3:=last_insert_id();
insert into DocCardRouteItem (DocCard, RouteItem, Passed) 
	select @docCard3, RouteItem, Passed from tmpDocCard3RouteItem;
	
-- Add DocCard: DocCard4 "Test DocCard4" into testDocumentFolder1 by DocCircUser3
-- under consideration by User3, passed User1, User2 RoutePointNum=3
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@testDocumentFolder1, @underConsDS, @docCircUser2, @docCircUser3, 'Test DocCard2', 'TTS000004', 
    '2007-04-24 11:35:00', false, 'T234', 'MS4', 'Evpo4', 
    false, 3,
    @docType2, '2007-04-23 12:00:00', '2007-04-01', 'this is the note of DocCard4',
    @institution2, @institution2
    );

set @docCard4:=last_insert_id();
insert into DocCardRouteItem (DocCard, RouteItem, Passed) 
	select @docCard4, RouteItem, Passed from tmpDocCard4RouteItem;
	
	
-- Add DocCard: DocCard5 "Test document card" into rootDocumentFolder by DocCircAdmin
-- under consideration by User2, User3; passed User1  RoutePointNum=2
insert into DocCard 
	(Folder, DocStatus, RegisteringUser, DcUser, Subject, InternalNumber, 
	RegistrationTime, AttachmentsAreChangeable, OriginalNumber, CustomSendingCompany, CustomReceivingCompany, 
    IsNew, RoutePointNum,
    DocType, LastMovement, OriginalDate, Note, SendingCompany, ReceivingCompany) Values 
    (@rootDocumentFolder, @underConsDS, @docCircUser3, @docCircAdmin, 'Test DocCard5', 'TTF000005', 
    '2007-05-24 11:35:00', false, 'T215', 'MS5', 'Evpo5', 
    false, 2,
    @docType1, '2007-05-23 12:00:00', '2007-05-01', 'this is the note of DocCard5',
    @institution1, @institution1
    );

set @docCard5:=last_insert_id();
insert into DocCardRouteItem (DocCard, RouteItem, Passed) 
	select @docCard5, RouteItem, Passed from tmpDocCard5RouteItem;